#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
create TEMPORARY table mid_lube_vip
select DISTINCT d.transaction_date minutes,d.station_id stationid,d.transaction_id transactionid,d.item_value itemvalue,
d.oillitre from
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre
from realtime.transaction_items_notoil_vip as A
join realtime.products as B
on A.product_code = B.product_code and B.station_id = 50006
join realtime.transactions_vip as C
on C.transaction_id = A.transaction_id
)d where d.department_id in (548,64);

select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(substr(oillitre,-2,1)),sum(itemvalue)
from mid_lube_vip
group by substr(minutes,1,16),stationID;
EOF
